﻿using Clirelmgt.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Dynamic;
using System.Linq;
using System.Text;
using System.Web;

namespace Clirelmgt.Manager
{
    public class DataHelper
    {
        public static string GetSqlSelectStatement(string tableName, Dictionary<string, object> fields, string whereCondition)
        {
            string query = "SELECT {0} FROM [{1}] WHERE {2}";
            string fieldList = "";
            foreach (KeyValuePair<string, object> field in fields)
            {
                fieldList += string.Format("[{0}],", field.Key);
            }

            fieldList = fieldList.Remove(fieldList.LastIndexOf(','), 1);

            return string.Format(query, fieldList, tableName, whereCondition);
        }

        public static string GetSqlSelectStatement(string tableName, MetaListPage listPage)
        {
            string query = "SELECT {0} {1} FROM [{2}] t";
            string topList = "";
            if (listPage.RecordCount > 0)
            {
                topList = string.Format("TOP {0}", listPage.RecordCount);
            }

            string fieldList = "";
            foreach (MetaFormField field in listPage.Fields)
            {
                if (field.DataType == FieldDataType.Table)
                {
                    fieldList += string.Format("(SELECT [Description] FROM [{0}] WHERE [No] = t.{1}) AS {1},", field.Options, field.Name);
                }
                else
                {
                    fieldList += string.Format("[{0}],", field.Name);
                }
            }

            fieldList = fieldList.Remove(fieldList.LastIndexOf(','), 1);

            query = string.Format(query, topList, fieldList, tableName);

            if (!string.IsNullOrWhiteSpace(listPage.FilterCondition))
            {
                query = string.Format("{0} WHERE {1}", query, listPage.FilterCondition);
            }

            if (!string.IsNullOrWhiteSpace(listPage.OrderCondition))
            {
                query = string.Format("{0} ORDER BY {1}", query, listPage.OrderCondition);
            }

            return query;
        }

        public static string GetSqlInsertStatement(string tableName, string prefixKey, Dictionary<string, object> fields, string userId)
        {
            StringBuilder queryBuilder = new StringBuilder();
            fields.Add("CREATEDDATE", DateTime.Now.ToString());
            fields.Add("CREATEDBY", userId);
            if (!fields.ContainsKey("OWNER"))
            {
                fields.Add("OWNER", userId);
            }
            else
            {
                if (fields["OWNER"] == null)
                {
                    fields["OWNER"] = userId;
                }
            }

            if (!string.IsNullOrEmpty(prefixKey))
            {
                fields["NO"] = null;
                queryBuilder.AppendLine("DECLARE @Ident INT");
                queryBuilder.AppendLine("DECLARE @IdentKey VARCHAR(30)");
                queryBuilder.AppendLine("INSERT INTO [{0}]({1}) VALUES({2})");
                queryBuilder.AppendLine("SET @Ident = (SELECT SCOPE_IDENTITY())");
                queryBuilder.AppendLine("SET @IdentKey = '{3}' + CONVERT(VARCHAR, @Ident)");
                queryBuilder.AppendLine("UPDATE [{0}] SET [No]=@IdentKey WHERE AutoID=@Ident");
                queryBuilder.AppendLine("SELECT @IdentKey");
            }
            else
            {
                queryBuilder.AppendLine("INSERT INTO [{0}]({1}) VALUES({2})");
            }
            
            string fieldList = "";
            string valueList = "";
            foreach (KeyValuePair<string, object> field in fields)
            {
                if (field.Value != null)
                {
                    fieldList += string.Format("[{0}],", field.Key);
                    valueList += string.Format("'{0}',", field.Value);
                }
            }

            fieldList = fieldList.Remove(fieldList.LastIndexOf(','), 1);
            valueList = valueList.Remove(valueList.LastIndexOf(','), 1);

            return string.Format(queryBuilder.ToString(), tableName, fieldList, valueList, prefixKey);
        }

        public static string GetSqlUpdateStatement(string tableName, Dictionary<string, object> fields, string whereCondition, string userId)
        {
            fields.Add("ModifiedDate", DateTime.Now.ToString());
            fields.Add("ModifiedBy", userId);
            string query = "UPDATE [{0}] SET {1} WHERE {2}";
            string fieldList = "";
            foreach (KeyValuePair<string, object> field in fields)
            {
                fieldList += string.Format("[{0}]='{1}',", field.Key, field.Value);
            }

            fieldList = fieldList.Remove(fieldList.LastIndexOf(','), 1);

            return string.Format(query, tableName, fieldList, whereCondition);
        }
    }

    public class Record : DynamicObject
    {
        public string TableName { get; set; }
        public Dictionary<string, object> TableFields { get; set; }
        public string PrefixKey { get; set; }
        public string UserID { get; set; }
        public string FromTable { get; set; }
        public string ToTable { get; set; }

        public Record()
        {
            TableFields = new Dictionary<string, object>();
        }

        public Record(string tableName)
        {
            TableFields = new Dictionary<string, object>();
            TableName = tableName.ToUpperInvariant();
        }

        public Record(string fromTable, string toTable)
        {
            FromTable = fromTable;
            ToTable = ToTable;
        }

        public override bool TryGetMember(GetMemberBinder binder, out object result)
        {
            string name = binder.Name.ToUpper();
            return TableFields.TryGetValue(name, out result);
        }

        public override bool TrySetMember(SetMemberBinder binder, object value)
        {
            bool result = false;

            if (TableFields.ContainsKey(binder.Name.ToUpper()))
            {
                TableFields[binder.Name.ToUpper()] = value;
                result = true;
            }
            
            return result;
        }

        public void GET(string key, params string[] columns)
        {
            TableFields.Clear();
            try
            {
                string query = "SELECT TOP 1 {0} FROM [{1}] WHERE [No] = '{2}'";
                string fieldList = "";
                if (columns != null && columns.Length > 0)
                {
                    foreach (string column in columns)
                    {
                        fieldList += string.Format("[{0}],", column);
                    }

                    fieldList = fieldList.Remove(fieldList.LastIndexOf(','), 1);
                }
                else
                {
                    fieldList = "*";
                }

                query = string.Format(query, fieldList.ToUpper(), TableName, key);

                DataSet ds = DataAccess.RunSelectStatement(query);
                if (ds.Tables.Count > 0)
                {
                    if (ds.Tables[0].Rows.Count > 0)
                    {
                        DataRow row = ds.Tables[0].Rows[0];
                        foreach (DataColumn column in ds.Tables[0].Columns)
                        {
                            if (TableFields.ContainsKey(column.ColumnName))
                            {
                                TableFields[column.ColumnName.ToUpperInvariant()] = row[column.ColumnName];
                            }
                            else
                            {
                                TableFields.Add(column.ColumnName.ToUpperInvariant(), row[column.ColumnName]);
                            }
                        }
                    }
                    else
                    {
                        throw new Exception("No record found!");
                    }
                }
                else
                {
                    throw new Exception("No record found!");
                }
            }
            catch
            {
                throw new Exception("Error while getting record");
            }
        }

        public void INIT()
        {
            TableFields.Clear();
            using (CRMDataContext dataContext = new CRMDataContext())
            {
                List<AppObject> appObjects = dataContext.AppObjects.Where(obj => (obj.Name == TableName && obj.ObjectType == "TABLE")).ToList();
                if (appObjects.Count > 0)
                {
                    MetaTable tableObject = Utility.DeserializeString<MetaTable>(appObjects[0].Metadata) as MetaTable;
                    foreach (MetaTableField field in tableObject.Fields)
                    {
                        TableFields.Add(field.Name.ToUpper(), null);
                    }

                    TableFields.Add("OWNER", null);

                    PrefixKey = tableObject.PrefixKey;
                }
                else
                {
                    throw new Exception("Invalid table object");
                }
            }
        }

        public void INIT(string userID)
        {
            UserID = userID;
            if (!string.IsNullOrEmpty(TableName))
            {
                INIT();
            }
        }

        public string INSERT()
        {
            string query = DataHelper.GetSqlInsertStatement(TableName, PrefixKey, TableFields, UserID);
            object result = DataAccess.ExecuteScalar(query);
            return result as string;
        }

        public void UPDATE(string key)
        {
            DataHelper.GetSqlUpdateStatement(TableName, TableFields, string.Format("[No]='{0}'", key), UserID);
        }

        public void LINK(string fromKey, string toKey)
        {
            using (CRMDataContext dataContext = new CRMDataContext())
            {
                dataContext.CreateTableLink(FromTable, fromKey, ToTable, toKey, UserID);
            }
        }

        public void DELINK(string fromKey, string toKey)
        {
            using (CRMDataContext dataContext = new CRMDataContext())
            {
                dataContext.DeleteTableLink(FromTable, fromKey, ToTable, toKey);
            }
        }
    }
}